# coding=utf-8
import os
import sqlite3
DB_PATH = os.path.join(os.path.dirname(__file__), '..', 'db')

class ProjectHandler(object):
    def __init__(self, project_name):
        self.db_file = os.path.join(DB_PATH, project_name+'.db')
        if os.path.isfile(self.db_file):	
            self.cx = sqlite3.connect(self.db_file)
            self.cu = self.cx.cursor()
        else:
            self.cx = sqlite3.connect(self.db_file)
            self.cu = self.cx.cursor()
            self.init_tables()

    def init_tables(self):
        print "init...."
        self.cu.execute('create table issue (id integer primary key,'
                        'subject varchar(255), submitter_id integer, status varchar(10))')
        self.cu.execute('create table attribute (id integer primary key, name varchar(80))')
        #self.cu.execute("insert into attribute values (null, '描述')")
        self.add_new_attribute('描述')
        self.cx.commit()
        self.cu.execute('create table attribute_value (id integer primary key,'
                        'issue_id integer, attribute_id integer, value varchar(255))')

    def add_new_attribute(self, attribute_name):
        u_string = unicode(attribute_name, 'utf-8')
        print u_string
        for each_att in self.get_attribute_names():
            print 'each', each_att
        if u_string in self.get_attribute_names():
            return False
        else:
            print 'insert', u_string
            self.cu.execute("insert into attribute values (null, '{}')".format(u_string.encode('utf-8')))
            self.cx.commit()
            return True

    def get_attribute_names(self):
        self.cu.execute('select * from attribute')
        all_attributes = self.cu.fetchall()
        print all_attributes
        return [x[1] for x in all_attributes]
        
    def create_issue(self, subject, submitter_id, attributes):
        attribute_names = self.get_attribute_names()
        for attribute_name in attributes:
            u_attribute_name = unicode(attribute_name, 'utf-8')
            if u_attribute_name not in attribute_names:
                print "error attribute name: "+ unicode(attribute_name, 'utf-8')
                return False
        u_subject = unicode(subject, 'utf-8')
        self.cu.execute("insert into issue values (null, '{}', {}, 'new')".format(u_subject.encode('utf-8'), submitter_id))
        issue_id = self.cu.lastrowid
        print "issue_id", issue_id
        self.cx.commit()
        for attribute_name in attributes:
            self.cu.execute("select id from attribute where name = '{}'".format(attribute_name))
            att_id = self.cu.fetchone()[0]
            print issue_id, att_id, attributes[attribute_name]
            att_value = unicode(attributes[attribute_name], 'utf-8').encode('utf-8')
            self.cu.execute("insert into attribute_value values (null, {}, {}, '{}')".format(issue_id, att_id, att_value))
        self.cx.commit()
        return True
        
    def read_issue(self, issue_id):
        self.cu.execute("select subject, submitter_id, status from issue where id = {}".format(issue_id))
        content = self.cu.fetchone()
        if not content:
            return {}
        subject, submitter_id, status = content[0], content[1], content[2]

        self.cu.execute("select a.name, v.value from attribute_value v join attribute a on v.attribute_id = a.id where v.issue_id = {}".format(issue_id))
        attributes = self.cu.fetchall()
        print content, attributes
        att_dict = {}
        for pair in attributes:
            att_dict[pair[0]] = pair[1]
        return {"subject": subject, "submitter_id": submitter_id, "status": status, "attributes": att_dict}
        
    def read_all_issues(self):
        self.cu.execute("select id from issue")
        ids = self.cu.fetchall()
        result = {}
        for one_id in ids:
            result[one_id[0]] = self.read_issue(one_id[0])
        return result

    def update_issue_subject(self, issue_id, subject):
        u8_subject = unicode(subject, 'utf-8').encode('utf-8')
        self.cu.execute("update issue set subject = '{}' where id = {}".format(u8_subject, issue_id))
        self.cx.commit()
        
    def update_issue_status(self, issue_id, status):
        u8_subject = unicode(status, 'utf-8').encode('utf-8')
        self.cu.execute("update issue set subject = '{}' where id = {}".format(u8_subject, issue_id))
        self.cx.commit()

    def update_attribute_value_for_issue(self, issue_id, attribute_name, attribute_value):
        attribute_names = self.get_attribute_names()
        u_attribute_name = unicode(attribute_name, 'utf-8')
        if u_attribute_name not in attribute_names:
            print "error update attribute {}".format(u_attribute_name)
            return False
        self.cu.execute("select id from attribute where name = '{}'".format(attribute_name))
        att_id = self.cu.fetchone()[0]
        self.cu.execute("update attribute_value set value = '{}' where issue_id = {} and attribute_id = {}".format(attribute_value, issue_id, att_id))
        self.cx.commit()
        return True

if __name__ == '__main__':
    dh = ProjectHandler("proj3")
    print dh.get_attribute_names()
    print dh.add_new_attribute('描述')
    print dh.add_new_attribute('图片')
    print dh.add_new_attribute('描述')
    print dh.get_attribute_names()
    dh.create_issue("第一个项目", 101, {"描述": "随便描述一下", "图片": "/abc/dd"})
    print dh.read_issue(1)
    print dh.read_issue(2)
    print dh.read_issue(5)
    dh.update_issue_subject(1, "new subject新")
    print dh.read_issue(1)
    dh.update_attribute_value_for_issue(1, "描述", "new miaoshu描述")
    print dh.read_issue(1)
    dh.update_issue_status(1, "pending")
    print dh.read_all_issues()
